# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import os

# 定义输入输出路径
input_file_path = r'data/12/竞店价格带数据分析-原始数据.xlsx'
output_dir = r'output/12'

# 确保输出目录存在
os.makedirs(output_dir, exist_ok=True)

# 加载Excel文件
data = pd.read_excel(input_file_path, sheet_name='Sheet1')

# 查看前几行数据以了解数据结构
print(data.head())

# 定义价格区间
bins = list(range(18, int(data['现价'].max()) + 20, 20))
labels = [f'{x}-{x+20}' for x in bins[:-1]]

# 添加一个新列来表示每个商品所属的价格区间
data['price_band'] = pd.cut(data['现价'], bins=bins, labels=labels, right=False)

# 计算98-118元区间的总销量
target_band_sales = data[data['price_band'] == '98-118']['30天销量'].sum()

print(f"98-118元价格区间的30天总销量是: {target_band_sales}")

# 计算每个价格区间的总销量
band_sales = data.groupby('price_band')['30天销量'].sum().reset_index()

# 找出客户接受度最高和最低的价格区间
max_band = band_sales.loc[band_sales['30天销量'].idxmax()]
min_band = band_sales.loc[band_sales['30天销量'].idxmin()]

# 计算总销量
total_sales = band_sales['30天销量'].sum()

# 计算各自占比
max_band_ratio = round(max_band['30天销量'] / total_sales * 100, 2)
min_band_ratio = round(min_band['30天销量'] / total_sales * 100, 2)

print(f"客户接受度最高的价格区间是: {max_band['price_band']}，占总销售比例 {max_band_ratio}%")
print(f"客户接受度最低的价格区间是: {min_band['price_band']}，占总销售比例 {min_band_ratio}%")

# 设置字体为SimSun
plt.rcParams['font.sans-serif'] = ['SimSun']
plt.rcParams['font.family'] = 'sans-serif'
plt.rcParams['font.sans-serif'] = ['SimSun']
plt.rcParams['axes.unicode_minus'] = False

# 绘制各价格区间的销量分布
plt.figure(figsize=(12, 6))
ax = sns.barplot(x='price_band', y='30天销量', data=band_sales)

# 在柱状图上方添加数值标签
for p in ax.patches:
    height = p.get_height()
    ax.text(p.get_x() + p.get_width() / 2., height + 5, f'{int(height)}',
            ha="center", va="bottom")

plt.title('各价格区间的30天销量')
plt.xlabel('价格区间')
plt.ylabel('30天销量')
plt.xticks(rotation=45)

# 保存图表到output\12目录
output_chart_path = os.path.join(output_dir, 'price_band_sales.png')
plt.tight_layout()
plt.savefig(output_chart_path)
plt.show()

# 保存分析结果到output\12目录
output_data_path = os.path.join(output_dir, 'price_band_analysis.xlsx')
with pd.ExcelWriter(output_data_path, engine='openpyxl') as writer:
    data.to_excel(writer, sheet_name='Original Data', index=False)
    band_sales.to_excel(writer, sheet_name='Band Sales', index=False)

print(f"图表已保存为: {output_chart_path}")
print(f"数据表已保存为: {output_data_path}")